﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    // NOTE: If you change the class name "Employee" here, you must also update the reference to "Employee" in App.config.

    public partial class MainService : IBranch
    {
        public int Branch_Using(int BranchID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@BranchID", BranchID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Branch_GetOne(int _BranchID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_GetOne", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", _BranchID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Branch_GetByRegionCode(string _RegionCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_GetByRegionCode", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_RegionCode", _RegionCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Branch_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Branch_GetList(out int ItemCount, int currentPage, int pageSize, string _BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;

        }

        public BranchInfo Branch_GetInfo(int BranchID)
        {
            BranchInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", BranchID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new BranchInfo();
                    retVal.ID = Convert.ToInt32(dr["Branch_ID"]);
                    retVal.InsType = Convert.ToInt32(dr["Branch_InsType"]);
                    retVal.Code = dr["Branch_Code"].ToString();
                    retVal.Name = dr["Branch_Name"].ToString();
                    retVal.Phone = dr["Branch_Phone"].ToString();
                    retVal.Fax = dr["Branch_Fax"].ToString();


                    retVal.Email = Globals.DB_GetString(dr["Branch_Email"]);
                    retVal.Branch_TaxCode = Globals.DB_GetString(dr["Branch_TaxCode"]);
                    retVal.Branch_BankAccount = Globals.DB_GetString(dr["Branch_BankAccount"]);
                    retVal.Branch_BankName = Globals.DB_GetString(dr["Branch_BankName"]);

                    retVal.Branch_ScheduledMan = Convert.ToString(dr["Branch_ScheduledMan"]);
                    retVal.Branch_BHXHOfficers = Convert.ToString(dr["Branch_BHXHOfficers"]);
                    retVal.Branch_BHXHBossofProvince = Convert.ToString(dr["Branch_BHXHBossofProvince"]);
                    retVal.Branch_BHXHBossofDistrict = Convert.ToString(dr["Branch_BHXHBossofDistrict"]);

                    retVal.Description = dr["Branch_Description"].ToString();
                    retVal.Address = dr["Branch_Address"].ToString();
                    retVal.RepName = dr["Branch_RepName"].ToString();
                    retVal.RepPos = dr["Branch_RepPos"].ToString();
                    retVal.BHXH = dr["Branch_BHXH"].ToString();
                    retVal.CloseBook = dr["Branch_CloseBook"].ToString();
                    retVal.FamilySalary = dr["Branch_FamilySalary"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_FamilySalary"]);
                    retVal.ManagementLevel = dr["Branch_ManagementLevel"].ToString();
                    retVal.MedicalInsurancePercent = dr["Branch_MedicalInsurancePercent"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_MedicalInsurancePercent"]);
                    retVal.MedicalInsurancePercentLabour = dr["Branch_MedicalInsurancePercentLabour"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_MedicalInsurancePercentLabour"]);
                    retVal.MinBasicSalary = dr["Branch_MinBasicSalary"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_MinBasicSalary"]);
                    retVal.SocialInsurancePercent = dr["Branch_SocialInsurancePercent"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_SocialInsurancePercent"]);
                    retVal.SocialInsurancePercentLabour = dr["Branch_SocialInsurancePercentLabour"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_SocialInsurancePercentLabour"]);
                    retVal.TotalDateWork = dr["Branch_TotalDateWork"].ToString();
                    retVal.TradeUnion = dr["Branch_TradeUnion"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_TradeUnion"]);
                    retVal.UnemployedIns = dr["Branch_UnemployedIns"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_UnemployedIns"]);
                    retVal.UnemployedInsCompany = dr["Branch_UnemployedInsCompany"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["Branch_UnemployedInsCompany"]);
                    retVal.Joining = dr["Branch_Joining"].ToString();
                    retVal.RegionCode = dr["Branch_RegionCode"].ToString();
                    retVal.InsurancePlace = dr["Branch_InsurancePlace"].ToString();
                    retVal.Createdate = dr["Branch_CreateDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Branch_CreateDate"]);
                    retVal.LastUpdate = dr["Branch_LastUpdate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["Branch_LastUpdate"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public int Branch_Insert(BranchInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Branch_Name", eInfo.Name);
            dbCmd.Parameters.AddWithValue("@Branch_Phone", eInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Branch_Fax", eInfo.Fax);

            dbCmd.Parameters.AddWithValue("@Branch_Email", eInfo.Email);
            dbCmd.Parameters.AddWithValue("@Branch_TaxCode", eInfo.Branch_TaxCode);
            dbCmd.Parameters.AddWithValue("@Branch_BankAccount", eInfo.Branch_BankAccount);
            dbCmd.Parameters.AddWithValue("@Branch_BankName", eInfo.Branch_BankName);


            dbCmd.Parameters.AddWithValue("@Branch_ScheduledMan", eInfo.Branch_ScheduledMan);
            dbCmd.Parameters.AddWithValue("@Branch_BHXHOfficers", eInfo.Branch_BHXHOfficers);
            dbCmd.Parameters.AddWithValue("@Branch_BHXHBossofProvince", eInfo.Branch_BHXHBossofProvince);
            dbCmd.Parameters.AddWithValue("@Branch_BHXHBossofDistrict", eInfo.Branch_BHXHBossofDistrict);
            dbCmd.Parameters.AddWithValue("@Branch_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Branch_Address", eInfo.Address);
            dbCmd.Parameters.AddWithValue("@Branch_RepName", eInfo.RepName);
            dbCmd.Parameters.AddWithValue("@Branch_RepPos", eInfo.RepPos);
            dbCmd.Parameters.AddWithValue("@Branch_BHXH", eInfo.BHXH);
            dbCmd.Parameters.AddWithValue("@Branch_MedicalInsurancePercent", eInfo.MedicalInsurancePercent);
            dbCmd.Parameters.AddWithValue("@Branch_SocialInsurancePercent", eInfo.SocialInsurancePercent);
            dbCmd.Parameters.AddWithValue("@Branch_MedicalInsurancePercentLabour", eInfo.MedicalInsurancePercentLabour);
            dbCmd.Parameters.AddWithValue("@Branch_SocialInsurancePercentLabour", eInfo.SocialInsurancePercentLabour);
            dbCmd.Parameters.AddWithValue("@Branch_MinBasicSalary", eInfo.MinBasicSalary);
            dbCmd.Parameters.AddWithValue("@Branch_UnemployedInsCompany", eInfo.UnemployedInsCompany);
            dbCmd.Parameters.AddWithValue("@Branch_ManagementLevel", eInfo.ManagementLevel);
            dbCmd.Parameters.AddWithValue("@Branch_Joining", eInfo.Joining);
            dbCmd.Parameters.AddWithValue("@Branch_TradeUnion", eInfo.TradeUnion);
            dbCmd.Parameters.AddWithValue("@Branch_FamilySalary", eInfo.FamilySalary);
            dbCmd.Parameters.AddWithValue("@Branch_CloseBook", eInfo.CloseBook);
            dbCmd.Parameters.AddWithValue("@Branch_UnemployedIns", eInfo.UnemployedIns);
            dbCmd.Parameters.AddWithValue("@Branch_TotalDateWork", eInfo.TotalDateWork);
            dbCmd.Parameters.AddWithValue("@Branch_RegionCode", eInfo.RegionCode);
            dbCmd.Parameters.AddWithValue("@Branch_InsurancePlace", eInfo.InsurancePlace);
            dbCmd.Parameters.AddWithValue("@Branch_InsType", eInfo.InsType);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Branch_Update(BranchInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@Branch_Code", eInfo.Code);
            dbCmd.Parameters.AddWithValue("@Branch_Name", eInfo.Name);
            dbCmd.Parameters.AddWithValue("@Branch_Phone", eInfo.Phone);
            dbCmd.Parameters.AddWithValue("@Branch_Fax", eInfo.Fax);

            dbCmd.Parameters.AddWithValue("@Branch_Email", eInfo.Email);
            dbCmd.Parameters.AddWithValue("@Branch_TaxCode", eInfo.Branch_TaxCode);
            dbCmd.Parameters.AddWithValue("@Branch_BankAccount", eInfo.Branch_BankAccount);
            dbCmd.Parameters.AddWithValue("@Branch_BankName", eInfo.Branch_BankName);

            dbCmd.Parameters.AddWithValue("@Branch_ScheduledMan", eInfo.Branch_ScheduledMan);
            dbCmd.Parameters.AddWithValue("@Branch_BHXHOfficers", eInfo.Branch_BHXHOfficers);
            dbCmd.Parameters.AddWithValue("@Branch_BHXHBossofProvince", eInfo.Branch_BHXHBossofProvince);
            dbCmd.Parameters.AddWithValue("@Branch_BHXHBossofDistrict", eInfo.Branch_BHXHBossofDistrict);
            dbCmd.Parameters.AddWithValue("@Branch_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@Branch_Address", eInfo.Address);
            dbCmd.Parameters.AddWithValue("@Branch_RepName", eInfo.RepName);
            dbCmd.Parameters.AddWithValue("@Branch_RepPos", eInfo.RepPos);
            dbCmd.Parameters.AddWithValue("@Branch_BHXH", eInfo.BHXH);
            dbCmd.Parameters.AddWithValue("@Branch_MedicalInsurancePercent", eInfo.MedicalInsurancePercent);
            dbCmd.Parameters.AddWithValue("@Branch_MedicalInsurancePercentLabour", eInfo.MedicalInsurancePercentLabour);
            dbCmd.Parameters.AddWithValue("@Branch_SocialInsurancePercent", eInfo.SocialInsurancePercent);
            dbCmd.Parameters.AddWithValue("@Branch_SocialInsurancePercentLabour", eInfo.SocialInsurancePercentLabour);
            dbCmd.Parameters.AddWithValue("@Branch_MinBasicSalary", eInfo.MinBasicSalary);
            dbCmd.Parameters.AddWithValue("@Branch_TotalDateWork", eInfo.TotalDateWork);
            dbCmd.Parameters.AddWithValue("@Branch_TradeUnion", eInfo.TradeUnion);
            dbCmd.Parameters.AddWithValue("@Branch_FamilySalary", eInfo.FamilySalary);
            dbCmd.Parameters.AddWithValue("@Branch_Joining", eInfo.Joining);
            dbCmd.Parameters.AddWithValue("@Branch_ManagementLevel", eInfo.ManagementLevel);
            dbCmd.Parameters.AddWithValue("@Branch_UnemployedIns", eInfo.UnemployedIns);
            dbCmd.Parameters.AddWithValue("@Branch_UnemployedInsCompany", eInfo.UnemployedInsCompany);
            dbCmd.Parameters.AddWithValue("@Branch_CloseBook", eInfo.CloseBook);
            dbCmd.Parameters.AddWithValue("@Branch_RegionCode", eInfo.RegionCode);
            dbCmd.Parameters.AddWithValue("@Branch_InsurancePlace", eInfo.InsurancePlace);
            dbCmd.Parameters.AddWithValue("@Branch_InsType", eInfo.InsType);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Branch_Delete(int BranchID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", BranchID);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool Branch_Compare(int BranchID, string BranchCode)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Branch_Compare", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Branch_ID", BranchID);
            dbCmd.Parameters.AddWithValue("@Branch_Code", BranchCode);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)

                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

    }
}
